package org.ccu.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.ccu.common.ConnectionManager;
import org.ccu.vo.Building;


public class BuildingDAO {
	private static Connection conn = ConnectionManager.getConnection();
	private static Statement st = null;
	private static ResultSet rs = null;

	

	public static boolean saveBuilding(String title, String content,
			String username) {

		try {
			st = conn.createStatement();
			String sql = "insert into building(title,content,building_id,user_id) values ('"
					+ title
					+ "','"
					+ content
					+ "',(select max(building_id) from building)+1,(select user_id from myu where user_name='"
					+ username + "')) ";
			System.out.println(sql);
			st.execute(sql);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;

	}

	public static List<Building> getBuilding() {
		List<Building> bs=new ArrayList<Building>(); 
		try {
			st = conn.createStatement();
			String sql = "select b.building_id,b.title,b.content,u.user_name from building b,myu u where b.user_id=u.user_id order by b.building_id desc";
			System.out.println(sql);
			rs = st.executeQuery(sql);
			
			while(rs.next()){
				Building b=new Building(rs.getInt("building_id"),rs.getString("title"),rs.getString("content"),rs.getString("user_name"));
				bs.add(b);
			}
			return bs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			Boolean b=ConnectionManager.close(st, rs);
			return bs;
		}

	}
	public static List<Building> getBuildingByID(String buildingid) {
		List<Building> bs=new ArrayList<Building>(); 
		try {
			st = conn.createStatement();
			
			String sql = "select b.building_id,b.title,b.content,u.user_name from building b,myu u where b.user_id=u.user_id and b.building_id="+buildingid+" order by b.building_id desc";
			System.out.println(sql);
			rs = st.executeQuery(sql);
			
			while(rs.next()){
				Building b=new Building(rs.getInt("building_id"),rs.getString("title"),rs.getString("content"),rs.getString("user_name"));
				bs.add(b);
			}
			return bs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		} finally {
			Boolean b=ConnectionManager.close(st, rs);
			return bs;
		}
		
	}
	
}
